package com.situ.company.web220111.score.dao.impl;

import com.situ.company.web220111.score.dao.IScoreDao;
import com.situ.company.web220111.score.model.ScoreModel;
import com.situ.company.web220111.util.FmtEmpty;
import com.situ.company.web220111.util.JDBCUtil;

import java.util.*;

public class ScoreDaoImpl implements IScoreDao {
    private static String table = "score";
    private static String cols = "code_emp,code_pro,score";

    @Override
    public String insert(ScoreModel model) {
        StringBuffer sql = new StringBuffer(" insert into ");
        sql.append(table).append('(').append(cols).append(")values(?,?,?)");
        System.out.println(model.getCodeEmp());
        return JDBCUtil.update(sql.toString(), model.getCodeEmp(), model.getCodePro(), model.getScore());
    }

    @Override
    public String deleteByCode(ScoreModel model) {
        StringBuffer sql = new StringBuffer("delete from");
        sql.append(table).append(" Where code_emp = ? and code_pro=? ");
        return JDBCUtil.update(sql.toString(), model.getCodeEmp(), model.getCodePro());
    }

    @Override
    public String delete(ScoreModel model) {
        StringBuffer sql = new StringBuffer("delete from ").append(table);
        List<Object> values = appendWhere(sql, model);
        System.out.println(sql);
        return JDBCUtil.update(sql.toString(), values);
    }

    @Override
    public String updateAll(ScoreModel model) {
        StringBuffer sql = new StringBuffer("update ");
        sql.append(table).append(" set score=? where code_emp=? and code_pro=? ");
        return JDBCUtil.update(sql.toString(), model.getScore(), model.getCodeEmp(), model.getCodePro());
    }

    @Override
    public String updateActive(ScoreModel model) {
        StringBuffer sql = new StringBuffer(" update ");
        sql.append(table).append(" set id=id ");
        List<Object> values = appendSet(sql, model);
        return JDBCUtil.update(sql.toString(), values);
    }

    @Override
    public List<ScoreModel> selectList(ScoreModel model) {
        StringBuffer sql = new StringBuffer("select id, ");
        sql.append(cols).append(",(select name from project where code = code_pro)pro_name,").append("(select name from employee where code = code_emp)emp_name").append(" from ").append(table);
        List<Object> values = appendWhere(sql, model);//加上相应条件和对应数据
        Map<String, String> fields = new HashMap<>();
        fields.put("id", "id");
        fields.put("codeEmp", "code_emp");
        fields.put("codePro", "code_pro");
        fields.put("score", "score");
        fields.put("empName", "emp_name");
        fields.put("proName", "pro_name");
        return JDBCUtil.querylist(sql.toString(), values, ScoreModel.class, fields);//将最终集合返回回去
    }

    @Override
    public ScoreModel selectModel(ScoreModel model) {
        StringBuffer sql = new StringBuffer("select id,");
        sql.append(cols).append(" from ").append(table);
        sql.append(" where code_emp = ? and code_pro = ? ");
        List<Object> values = Arrays.asList(model.getCodeEmp(), model.getCodePro());
        Map<String, String> fields = new HashMap<>();
        fields.put("id", "id");
        fields.put("codeEmp", "code_emp");
        fields.put("codePro", "code_pro");
        fields.put("score", "score");
        return JDBCUtil.queryModel(sql.toString(), values, ScoreModel.class, fields);

    }

    @Override
    public Integer selectCount(ScoreModel model) {
        StringBuffer sql = new StringBuffer(" select count(1) from score ");
        List<Object> vals = appendWhere(sql, model);
        return JDBCUtil.queryInt(sql.toString(), vals);
    }

    private List<Object> appendWhere(StringBuffer sql, ScoreModel model) {
        sql.append(" where 1=1 ");
        List<Object> values = new ArrayList<>();
        String codePro = model.getCodePro();
        if (!FmtEmpty.isEmpty(codePro)) {
            sql.append(" and code_pro like ? ");
            values.add(codePro);
        }
        String codeEmp = model.getCodeEmp();
        if (!FmtEmpty.isEmpty(codeEmp)) {
            sql.append(" and code_emp like ? ");
            values.add(codeEmp);
        }
        String score = model.getScore();
        if (!FmtEmpty.isEmpty(score)) {
            sql.append(" and score like ? ");
            values.add(score);
        }

        return values;
    }

    private List<Object> appendSet(StringBuffer sql, ScoreModel model) {//问号替换的数据填充进来
        List<Object> values = new ArrayList<>();
        String score = model.getScore();
        if (score != null) {//判断有无对象
            sql.append(" ,score = ? ");
            values.add(score);
        }
        sql.append(" where code_emp=? and code_pro=? ");
        values.add(model.getCodeEmp());
        values.add(model.getCodePro());
        return values;
    }
}
